package od.igor.petrov.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import od.igor.petrov.utils.TestTableBean;

public class DerbyDAO {
	
	/* ========================================== *
	 *     BASIC MANIPULATIONS WITH DATABASE
	 * ========================================== */
	protected Connection retrieveConnection() {
		try {
			if (conn == null || conn.isClosed()) {
				Class.forName(DRIVER);
				System.out.println("Driver loaded!");
				conn = DriverManager.getConnection(CONNECTION_URL);
				System.out.println("Connection really opened");
			}
		} catch (ClassNotFoundException e) {
			System.out.println("Problems with driver");
		} catch (SQLException e) {
			System.out.println("Some error");
		} finally {
			System.out.println("Had chance to open connection");
		}
		return conn;
	}
	
	protected void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
				System.out.println("Closing connection");
			}
		} catch (SQLException e) {
			System.out.println("Failed to close connection");
		} finally {
			conn = null;
		}
	}
	
	
	/* =============================================== *
	 *                     C R U D
	 * =============================================== */
	protected void createTable() {
		try {
			s = retrieveConnection().createStatement();
			if (tableExists()) {
				System.out.println("Found previous version of table\nDropping");
				s.execute(DROP_TABLE);
			}
			s.execute(CREATE_TABLE);
		} catch (SQLException e) {
			System.out.println("Error while creating statement");
		}
	}
	
	protected boolean tableExists() {
		try {
			s = retrieveConnection().createStatement();
			s.execute(TEST_STATEMENT);
		} catch (SQLException e) {
			String err = (e).getSQLState();
			if (err.equals(ERROR_TABLE_EXISTS)) {
				return false;
			}
		}
		return true;
	}
	
	public void insertValues(){
		createTable();
		try {
			ps = retrieveConnection().prepareStatement(INSERT_VALUES);
			for (int i = 0; i < 10; i++) {
				ps.setInt(1, i);
				ps.setString(2, "value" + i);
				switch (i%3) {
				case 0:
					ps.setString(3, "Firm-0");
					break;
				case 1:
					ps.setString(3, "Firm-1");
					break;
				case 2:
					ps.setString(3, "Firm-2");
					break;
				default:
					ps.setString(3, "Dafault");
					break;
				}
				ps.executeUpdate();
			}
			System.out.println("Inserted some values");
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			System.out.println("Error while creating prepared statement");
		} finally {
			ps = null;
			closeConnection();
		}
	}
	
	public ArrayList<TestTableBean> selectValues(){
		try {
			s = retrieveConnection().createStatement();
			results = s.executeQuery(SELECT_VALUES);
			while (results.next()) {
				result.add(new TestTableBean(results.getInt(1), results.getString(2), results.getString(3)));
			}
			if (results != null) {
				results.close();
			}
		} catch (SQLException e) {
			System.out.println("Some error with getting SQL");
			return null;
		} finally {
			results = null;
			closeConnection();
		}
		return result;
	}

	private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
	private static final String DB_NAME = "TestDB";
	private static final String CONNECTION_URL = "jdbc:derby:" + DB_NAME + ";create=true";

	private static final String CREATE_TABLE = "CREATE TABLE TESTTABLE(ID INT NOT NULL, ITEM VARCHAR(10) NOT NULL, FIRM VARCHAR(10) NOT NULL)";
	private static final String INSERT_VALUES = "INSERT INTO TESTTABLE VALUES(?, ?, ?)";
	private static final String SELECT_VALUES = "SELECT * FROM TESTTABLE";
	private static final String DROP_TABLE = "DROP TABLE TESTTABLE";

	private static final String TEST_STATEMENT = "UPDATE TESTTABLE SET ID = 1, ITEM = 'TEST' WHERE 1 = 3";
	private static final String ERROR_TABLE_EXISTS = "42X05";

	private Connection conn = null;
	private Statement s = null;
	private PreparedStatement ps = null;
	private ResultSet results = null;
	
	private ArrayList<TestTableBean> result = new ArrayList<TestTableBean>();
}
